﻿using System.Data;
using MvcApplication2.Models.DTO;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.IO;

namespace MvcApplication2.Models.Services
{
    public class SanPhamService
    {
        public static Tuple<int, List<SanPham>> GetListSanPhamInSaleTime(int? idLoaiSanPham, int trang)
        {
            MyDataProvider.Open();
            int soTrang = 0;
            var sanPhamList = new List<SanPham>();
            var thamSo =Utility.ToList<ThamSo>(MyDataProvider.excuteQuery("spu_ThamSo_getAll"))[0];
            int soLuongSach = 0;
            if(idLoaiSanPham==null)
            {
                soLuongSach = (int) MyDataProvider.excuteScalar("spu_sanpham_getCountInSaleTime");
            }
            else
            {
                var parameterList = new List<SqlParameter>();
                parameterList.Add(new SqlParameter("@IDLoaiSanPham", SqlDbType.Int));
                parameterList[parameterList.Count - 1].Value = idLoaiSanPham;
                soLuongSach = (int)MyDataProvider.excuteScalarWithParameters("spu_sanpham_getCountInSaleTimeByIDLoaiSanPham", parameterList);
            }
            soTrang = (int)Math.Ceiling((double)soLuongSach/thamSo.SoSanPhamTrenTrang);

            var sqlParameterList = new List<SqlParameter>();
            sqlParameterList.Add(new SqlParameter("@start",SqlDbType.Int));
            sqlParameterList[sqlParameterList.Count - 1].Value = (trang - 1) * thamSo.SoSanPhamTrenTrang;
            sqlParameterList.Add(new SqlParameter("@end",SqlDbType.Int));
            sqlParameterList[sqlParameterList.Count - 1].Value = (trang - 1)*thamSo.SoSanPhamTrenTrang+thamSo.SoSanPhamTrenTrang;
            if(idLoaiSanPham==null)
            {
                sanPhamList = Utility.ToList<SanPham>(MyDataProvider.excuteQueryWithParameters("spu_sanpham_getLimitInSaleTime",
                                                                       sqlParameterList));
            }
            else
            {
                sqlParameterList.Add(new SqlParameter("@IDLoaiSanPham", SqlDbType.Int));
                sqlParameterList[sqlParameterList.Count - 1].Value = idLoaiSanPham;
                sanPhamList = Utility.ToList<SanPham>(MyDataProvider.excuteQueryWithParameters("spu_sanpham_getByIdLoaiSanPhamLimitInSaleTime",
                                                                       sqlParameterList));
            }
            MyDataProvider.Close();
            return new Tuple<int, List<SanPham>>(soTrang,sanPhamList);
        }

        public static SanPham GetByIdSanPham(int idSanPham)
        {
            MyDataProvider.Open();
            var sqlParameterList = new List<SqlParameter>();
            sqlParameterList.Add(new SqlParameter("@ID", SqlDbType.Int));
            sqlParameterList[sqlParameterList.Count - 1].Value = idSanPham;
            var sanPham =
                Utility.ToList<SanPham>(MyDataProvider.excuteQueryWithParameters("spu_sanpham_getById", sqlParameterList))
                    [0];
            MyDataProvider.Close();
            return sanPham;
        }

        public static string ThemSanPham(SanPham sanPham,HttpPostedFileBase file,string folderUpload)
        {
            MyDataProvider.Open();
            var transaction = MyDataProvider.sqlConnection.BeginTransaction(IsolationLevel.RepeatableRead);
            try
            {
                var sqlParameterList = new List<SqlParameter>();
                sqlParameterList.Add(new SqlParameter("@TenSanPham", sanPham.TenSanPham));
                sqlParameterList.Add(new SqlParameter("@IDLoaiSanPham", SqlDbType.Int));
                sqlParameterList[sqlParameterList.Count - 1].Value = sanPham.IdLoaiSanPham;
                sqlParameterList.Add(new SqlParameter("@Gia", SqlDbType.Decimal));
                sqlParameterList[sqlParameterList.Count - 1].Value = sanPham.Gia;
                sqlParameterList.Add(new SqlParameter("@SoLuong", SqlDbType.Int));
                sqlParameterList[sqlParameterList.Count - 1].Value = sanPham.SoLuong;
                sqlParameterList.Add(new SqlParameter("@DiemThuong", SqlDbType.Int));
                if(sanPham.DiemThuong==null)
                {
                    sqlParameterList[sqlParameterList.Count - 1].Value = DBNull.Value;
                }
                else
                {
                    sqlParameterList[sqlParameterList.Count - 1].Value = sanPham.DiemThuong;
                }
                sqlParameterList.Add(new SqlParameter("@ThoiGianBatDauBan", sanPham.ThoiGianBatDauBan));
                sqlParameterList.Add(new SqlParameter("@ThoiGianKetThucBan", sanPham.ThoiGianKetThucBan));
                sqlParameterList.Add(new SqlParameter("@ThoiGianBatDauNhanHang", sanPham.ThoiGianBatDauNhanHang));
                sqlParameterList.Add(new SqlParameter("@ThoiGianKetThucNhanHang", sanPham.ThoiGianKetThucNhanHang));
                sqlParameterList.Add(new SqlParameter("@DiemNhanThuong", SqlDbType.Int));
                if (sanPham.DiemNhanThuong == null)
                {
                    sqlParameterList[sqlParameterList.Count - 1].Value = DBNull.Value;
                }
                else
                {
                    sqlParameterList[sqlParameterList.Count - 1].Value = sanPham.DiemNhanThuong;
                }
                sqlParameterList.Add(new SqlParameter("@HinhAnh", DBNull.Value));
                var id = (int) MyDataProvider.excuteScalarWithParameters("spu_sanpham_add", sqlParameterList,transaction);
                if(file!=null)
                {
                    string fileName = id + Path.GetExtension(file.FileName);
                    string linkAccess = Path.Combine(folderUpload, fileName);
                    var sqlParameterList1 = new List<SqlParameter>();
                    sqlParameterList1.Add(new SqlParameter("@ID", SqlDbType.Int));
                    sqlParameterList1[sqlParameterList1.Count - 1].Value = id;
                    sqlParameterList1.Add(new SqlParameter("@TenSanPham", sanPham.TenSanPham));
                    sqlParameterList1.Add(new SqlParameter("@IDLoaiSanPham", SqlDbType.Int));
                    sqlParameterList1[sqlParameterList1.Count - 1].Value = sanPham.IdLoaiSanPham;
                    sqlParameterList1.Add(new SqlParameter("@Gia", SqlDbType.Decimal));
                    sqlParameterList1[sqlParameterList1.Count - 1].Value = sanPham.Gia;
                    sqlParameterList1.Add(new SqlParameter("@SoLuong", SqlDbType.Int));
                    sqlParameterList1[sqlParameterList1.Count - 1].Value = sanPham.SoLuong;
                    sqlParameterList1.Add(new SqlParameter("@DiemThuong", SqlDbType.Int));
                    if (sanPham.DiemThuong == null)
                    {
                        sqlParameterList1[sqlParameterList1.Count - 1].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlParameterList1[sqlParameterList1.Count - 1].Value = sanPham.DiemThuong;
                    }
                    sqlParameterList1.Add(new SqlParameter("@ThoiGianBatDauBan", sanPham.ThoiGianBatDauBan));
                    sqlParameterList1.Add(new SqlParameter("@ThoiGianKetThucBan", sanPham.ThoiGianKetThucBan));
                    sqlParameterList1.Add(new SqlParameter("@ThoiGianBatDauNhanHang", sanPham.ThoiGianBatDauNhanHang));
                    sqlParameterList1.Add(new SqlParameter("@ThoiGianKetThucNhanHang", sanPham.ThoiGianKetThucNhanHang));
                    sqlParameterList1.Add(new SqlParameter("@DiemNhanThuong", SqlDbType.Int));
                    if (sanPham.DiemNhanThuong == null)
                    {
                        sqlParameterList1[sqlParameterList1.Count - 1].Value = DBNull.Value;
                    }
                    else
                    {
                        sqlParameterList1[sqlParameterList1.Count - 1].Value = sanPham.DiemNhanThuong;
                    }
                    sqlParameterList1.Add(new SqlParameter("@HinhAnh", DBNull.Value));
                    sqlParameterList1[sqlParameterList1.Count - 1].Value = fileName;
                    MyDataProvider.updateQueryWithParameters("spu_sanpham_update",sqlParameterList1,transaction);
                    file.SaveAs(linkAccess);
                }
                transaction.Commit();
                MyDataProvider.Close();
                return "Thêm sản phẩm thành công";
            }
            catch (Exception e)
            {
                transaction.Rollback();
                MyDataProvider.Close();
                return e.Message;
            }
        }
    }
}